/*
*  Copyright 2019-2020 Zheng Jie
*
*  Licensed under the Apache License, Version 2.0 (the "License");
*  you may not use this file except in compliance with the License.
*  You may obtain a copy of the License at
*
*  http://www.apache.org/licenses/LICENSE-2.0
*
*  Unless required by applicable law or agreed to in writing, software
*  distributed under the License is distributed on an "AS IS" BASIS,
*  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
*  See the License for the specific language governing permissions and
*  limitations under the License.
*/
package me.zhengjie.modules.biz.repository;

import me.zhengjie.modules.biz.domain.Lab;
import me.zhengjie.modules.biz.domain.Sensor;
import org.springframework.data.domain.Page;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.domain.Pageable;

import java.util.List;
import java.util.Map;
import java.util.Optional;

/**
* @website https://eladmin.vip
* @author Admin
* @date 2023-02-17
**/
public interface LabRepository extends JpaRepository<Lab, Integer>, JpaSpecificationExecutor<Lab> {
    @Query(value = "SELECT \n" +
            "    l.id,\n" +
            "    l.administrator,\n" +
            "    l.name,\n" +
            "    d.data temperature,\n" +
            "    q.data humidity,\n" +
            "    w.data pressure,\n" +
            "    e.data voc\n" +
            "FROM\n" +
            "    hj_lab l\n" +
            "        LEFT JOIN\n" +
            "    hj_sensor d ON (l.id = d.lab_id AND d.type = 1)\n" +
            "        LEFT JOIN\n" +
            "    hj_sensor q ON (l.id = q.lab_id AND q.type = 2)\n" +
            "        LEFT JOIN\n" +
            "    hj_sensor w ON (l.id = w.lab_id AND w.type = 3)\n" +
            "        LEFT JOIN\n" +
            "    hj_sensor e ON (l.id = e.lab_id AND e.type = 5)\n" +
            "WHERE\n" +
            "    (d.data IS NOT NULL OR q.data IS NOT NULL\n" +
            "        OR w.data IS NOT NULL\n" +
            "        OR e.data IS NOT NULL);"
            , nativeQuery = true)
    List<Map<String, Object>> queryLabAll();


    @Query(value = "SELECT * FROM hj_lab where id = ?1",nativeQuery = true)
    Lab getLab(int id);
    @Query(value = "select t.name lab_name, t.room_number, h.exhaust_air_rate exhaustAirRate,h.temperature temperatureExhaustFan,h.humidity humidityExhaustFan, h.name exhaust_air_rate_name, d.data temperature, q.data humidity, w.data pressure, r.data voc from hj_lab t " +
            "left join hj_exhaust_fan h on (t.id = h.lab_id)\n" +
            "left join hj_sensor d on (t.id = d.lab_id and d.type = 1)" +
            "left join hj_sensor q on (t.id = q.lab_id and q.type = 2)" +
            "left join hj_sensor w on (t.id = w.lab_id and w.type = 3)" +
            "left join hj_sensor r on (t.id = r.lab_id and r.type = 5)" +
            "where t.room_number = ?1 and" +
            "(d.data IS NOT NULL OR q.data IS NOT NULL\n" +
            "        OR w.data IS NOT NULL\n" +
            "        OR r.data IS NOT NULL)", nativeQuery = true)
    Map<String, Object> queryLabByRoomNumber(String roomNumber);

    @Query(value = "select t.id, t.room_number, t.name lab_name, t.admin_name, d.data temperature, q.data humidity, " +
            "w.data pressure, r.data voc, " +
            "if(d.data > d.high_threshold_value and d.data < d.low_threshold_value, 1, 0) t_alarm, " +
            "if(w.data > w.high_threshold_value and w.data < w.low_threshold_value, 1, 0) p_alarm, " +
            "if(q.data > q.high_threshold_value and q.data < q.low_threshold_value, 1, 0) h_alarm, " +
            "if(r.data > r.high_threshold_value and r.data < r.low_threshold_value, 1, 0) v_alarm " +
            "from hj_lab t " +
            "left join hj_sensor d on (t.id = d.lab_id and d.type = 1)" +
            "left join hj_sensor q on (t.id = q.lab_id and q.type = 2)" +
            "left join hj_sensor w on (t.id = w.lab_id and w.type = 3)" +
            "left join hj_sensor r on (t.id = r.lab_id and r.type = 5)" +
            "where t.floor = ?1 and" +
            "(d.data IS NOT NULL OR q.data IS NOT NULL\n" +
            "        OR w.data IS NOT NULL\n" +
            "        OR r.data IS NOT NULL)", nativeQuery = true)
    List<Map<String, Object>> queryLabByFloor(Integer floor);

    @Query(value = "SELECT distinct t.administrator administrator, t.admin_name adminName FROM hanjiang.hj_lab t", nativeQuery = true)
    List<Map<String, Object>> queryAdminAll();

    @Query(value = "select t.adminName from (select u.user_id administrator, u.username adminName from hanjiang.sys_user u where u.user_id in (SELECT r.user_id FROM hanjiang.sys_users_roles r where r.role_id = 3)) t where t.administrator = 15", nativeQuery = true)
    Map<String, String> findByadminId(Integer administrator);

    @Query(value = "SELECT id, name, 2 type FROM hanjiang.hj_lab "
//            +
//            "union select id, name, 1 type FROM hanjiang.hj_exhaust_fan " +
//            "union select id, name, 1 type FROM hanjiang.hj_fresh_air_ventilator " +
//            "union select id, name, 1 type FROM hanjiang.hj_fume_hood " +
//            "union select id, name, 1 type FROM hanjiang.hj_residual_air_volume"
            , nativeQuery = true)
    List<Map<String, Object>> queryLeftAll();

    @Query(value = "select 1 id, \"温度\" name, d.updated_at c_time,  d.data c_value  from hj_lab t " +
            "left join hj_sensor d on (t.id = d.lab_id and d.type = 1)" +
            "where t.id = ?1", nativeQuery = true)
    Map<String, Object> queryLabTemp(Integer id);

    @Query(value = "select 2 id, \"压力\" name, d.updated_at c_time,  d.data c_value  from hj_lab t " +
            "left join hj_sensor d on (t.id = d.lab_id and d.type = 3)" +
            "where t.id = ?1", nativeQuery = true)
    Map<String, Object> queryLabPressure(Integer id);

    @Query(value = "select 3 id, \"湿度\" name, d.updated_at c_time,  d.data c_value  from hj_lab t " +
            "left join hj_sensor d on (t.id = d.lab_id and d.type = 2)" +
            "where t.id = ?1", nativeQuery = true)
    Map<String, Object> queryLabHumidity(Integer id);

    @Query(value = "select 4 id, \"VOC\" name, d.updated_at c_time,  d.data c_value  from hj_lab t " +
            "left join hj_sensor d on (t.id = d.lab_id and d.type = 5)" +
            "where t.id = ?1", nativeQuery = true)
    Map<String, Object> queryLabVOC(Integer id);

    @Query(value = "SELECT id,name FROM hj_lab ",nativeQuery = true)
    List<Map<String, Object>> cabinetList();

    @Query(value = "select u.user_id administrator, u.username adminName from sys_user u where u.user_id in (SELECT r.user_id FROM sys_users_roles r where r.role_id = 3)",nativeQuery = true)
    List<Map<String, Object>> findByRoleId();
}
